package com.linedata.acq.test;

/*
 * Date: 2010-12-10
 * Time: 16:24:30
 */

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.nodes.TParameterDeclaration;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlAssignStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlBlock;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlIfStmt;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlVarDeclStmt;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.StringWriter;

public class analyzePLSQLProcedure {

	public static void main(String args[]) {

		TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);

		String s = "./resources/plsql/PRC.sql";
		File file = new File(s);
		if (!file.exists()) {
			System.out.println("File not exists:" + s);
			return;
		}

		sqlparser.sqltext = loadFile(file);

		int ret = sqlparser.parse();
		if (ret != 0) {
			System.out.println(sqlparser.getErrormessage());
			return;
		}

		TCustomSqlStatement sql = sqlparser.sqlstatements.get(0);
		System.out.println("SQL Statement: " + sql.sqlstatementtype);

		TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure) sql;
		System.out.println("Procedure name: " + procedure.getProcedureName().toString());
		System.out.println("Parameters:");

		TParameterDeclaration param = null;
		for (int i = 0; i < procedure.getParameterDeclarations().size(); i++) {
			param = procedure.getParameterDeclarations().getParameterDeclarationItem(i);
			System.out.println("\tName:" + param.getParameterName().toString());
			System.out.println("\tDatatype:" + param.getDataType().toString());
			System.out.println("\tIN/OUT:" + param.getMode());
		}

		TStatementList declareStatements = procedure.getDeclareStatements();
		System.out.println("declare statements: " + declareStatements.size());
		TCustomSqlStatement declareStatement = null;
		TPlsqlVarDeclStmt variableDelcare = null;
		for (int i = 0; i < declareStatements.size(); i++) {
			declareStatement = declareStatements.get(i);
			System.out.println("SQL Statement: " + declareStatement.sqlstatementtype);
			variableDelcare = (TPlsqlVarDeclStmt) declareStatement;

			switch (variableDelcare.getWhatDeclared()) {
			case TPlsqlVarDeclStmt.whatDeclared_variable:
				System.out.println("\tVariable Name:" + variableDelcare.getElementName().toString());
				System.out.println("\tVariable Datatype:" + variableDelcare.getDataType().toString());
				break;
			case TPlsqlVarDeclStmt.whatDeclared_constant:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_exception:
				System.out.println("\tException:" + variableDelcare.getElementName().toString());
				break;
			case TPlsqlVarDeclStmt.whatDeclared_subtype:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_pragma_autonomous_transaction:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_pragma_exception_init:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_pragma_serially_reusable:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_pragma_restrict_references:
				break;
			case TPlsqlVarDeclStmt.whatDeclared_pragma_timestamp:
				break;
			}
		}

		TStatementList bodyStatements = procedure.getBodyStatements();
		System.out.println("body statements: " + bodyStatements.size());
		TCustomSqlStatement bodyStatement = null;
		for (int i = 0; i < bodyStatements.size(); i++) {
			bodyStatement = bodyStatements.get(i);
			System.out.println("SQL Statement: " + bodyStatement.sqlstatementtype);
			switch (bodyStatement.sqlstatementtype) {
			case sstplsql_block:
				TPlsqlBlock blk = (TPlsqlBlock) bodyStatement;
				TStatementList blkStatements = blk.getBodyStatements();
				TCustomSqlStatement blkStatement = null;
				for (int j = 0; j < blkStatements.size(); j++) {
					blkStatement = blkStatements.get(j);
					System.out.println("\tSQL BLK Statement: " + blkStatement.sqlstatementtype);
					switch (blkStatement.sqlstatementtype) {
					case sstselect:
						System.out.println("\txxxx: " + blkStatement.toString());
						break;
					default:
						break;
					}
				}
				break;
			case sstplsql_assignstmt:
				TPlsqlAssignStmt assign = (TPlsqlAssignStmt) bodyStatement;
				System.out.println("left: " + assign.getLeft().toString());
				System.out.println("right: " + assign.getExpression().toString());
				break;
			case sstplsql_ifstmt:
				TPlsqlIfStmt ifstmt = (TPlsqlIfStmt) bodyStatement;
				System.out.println("condition: " + ifstmt.getCondition().toString());

				if (ifstmt.getThenStatements().size() > 0) {
					System.out.println("then statement:");
					for (int j = 0; j < ifstmt.getThenStatements().size(); j++) {
						System.out.println("\tStatement type: " + ifstmt.getThenStatements().get(j).sqlstatementtype);
						System.out.println("\tStatement text: " + ifstmt.getThenStatements().get(j).toString());
					}
				}

				if (ifstmt.getElseifStatements().size() > 0) {
					System.out.println("else if statement:");
					for (int j = 0; j < ifstmt.getElseifStatements().size(); j++) {
						System.out.println("\tStatement type: " + ifstmt.getElseifStatements().get(j).sqlstatementtype);
						System.out.println("\tStatement text: " + ifstmt.getElseifStatements().get(j).toString());
					}
				}

				if (ifstmt.getElseStatements().size() > 0) {
					System.out.println("else statement:");
					for (int j = 0; j < ifstmt.getElseStatements().size(); j++) {
						System.out.println("\tStatement type: " + ifstmt.getElseStatements().get(j).sqlstatementtype);
						System.out.println("\tStatement text: " + ifstmt.getElseStatements().get(j).toString());
					}
				}

				break;
			default:
				break;
			}
		}

	}

	/**
	 * 
	 */
	// public void procAppelee () {
	//
	// TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure) sql;
	// TStatementList bodyStatements = procedure.getBodyStatements();
	// System.out.println("body statements: " + bodyStatements.size());
	// TCustomSqlStatement bodyStatement = null;
	// for (int i = 0; i < bodyStatements.size(); i++) {
	// bodyStatement = bodyStatements.get(i);
	// switch (bodyStatement.sqlstatementtype) {
	// case sstplsql_block:
	// TPlsqlBlock blk = (TPlsqlBlock) bodyStatement;
	// TStatementList blkStatements = blk.getBodyStatements();
	// TCustomSqlStatement blkStatement = null;
	// for (int j = 0; j < blkStatements.size(); j++) {
	// blkStatement = blkStatements.get(j);
	// switch (blkStatement.sqlstatementtype) {
	// case sstplsql_procbasicstmt:
	// System.out.println(blkStatement.toString());
	// break;
	// default:
	// break;
	// }
	// }
	// break;
	// default:
	// break;
	// }
	// }
	// }

	/**
	 * 
	 */
	// public void except () {
	//
	// TPlsqlCreateProcedure procedure = (TPlsqlCreateProcedure) sql;
	//
	// analyzeException(procedure);
	// }
	//
	// private void analyzeException (TPlsqlStmt procedure) {
	//
	// TExceptionClause exceptStatements = procedure.getExceptionClause();
	// TExceptionHandlerList exceptStmtHandlers = exceptStatements
	// .getHandlers();
	// TExceptionHandler exceptStmtHandler = null;
	// for (int k = 0; k < exceptStmtHandlers.size(); k++) {
	// exceptStmtHandler = exceptStmtHandlers.getExceptionHandler(k);
	// System.out.println(exceptStmtHandler.toString());
	// }
	//
	// TStatementList bodyStatements = procedure.getBodyStatements();
	// TCustomSqlStatement bodyStatement = null;
	// for (int i = 0; i < bodyStatements.size(); i++) {
	//
	// bodyStatement = bodyStatements.get(i);
	//
	// switch (bodyStatement.sqlstatementtype) {
	// case sstplsql_block:
	//
	// TPlsqlBlock blk = (TPlsqlBlock) bodyStatement;
	// analyzeException(blk);
	// break;
	// default:
	// break;
	// }
	// }
	// }

	/**
	 * Loads the specified file into a String representation
	 * 
	 * @author Stephane Nicoll - Infonet FUNDP
	 * @version 0.1
	 */
	public static String loadFile(File f) {
		try {
			BufferedInputStream in = new BufferedInputStream(new FileInputStream(f));
			StringWriter out = new StringWriter();
			int b;
			while ((b = in.read()) != -1)
				out.write(b);
			out.flush();
			out.close();
			in.close();
			return out.toString();
		} catch (IOException ie) {
			ie.printStackTrace();
		}
		return null;
	}

}